//
//  ViewController.m
//  FMDBTest
//
//  Created by Song on 2018/4/17.
//  Copyright © 2018年 Song. All rights reserved.
//

#import "ViewController.h"
//#import "FMDatabase.h"
#import <FMDatabase.h>
#import "Student.h"

@interface ViewController () {
    // 全局
    FMDatabase *db; // FMDB 对象
    int markStudent; // 学生标记
    NSString *docPath; // 数据库地址
}

@end

@implementation ViewController

- (void)viewDidLoad {
    // 创建数据库
    [self createSQLite];
    
    // 插入数据
//    for (int i = 0; i < 5; i++) {
//        [self insertData];
//    }
    
    // 删除数据
//    [self deleteData];
    
    // 修改数据
//    [self changeData];
    
    // 查询数据
    [self printData];
}

// 创建数据库
-(void)createSQLite {
    // 1. 数据库路径
    docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSLog(@"数据库文件路径：%@",docPath);
    markStudent = 1;
    
    NSString *fileName = [docPath stringByAppendingPathComponent:@"student.sqlite"];
//    NSLog(@"拼接filename：%@",fileName);
    
    // 2. 获取数据库
    db = [FMDatabase databaseWithPath:fileName];
    if ([db open]) {
        NSLog(@"打开数据库成功");
    }else {
        NSLog(@"打开数据库失败");
    }
    
    // 3. 创建表 : t_student 和 gsy_student
    BOOL result = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS t_student (id integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, age integer NOT NULL, gsysex text NOT NULL);"];
    if (result) {
        NSLog(@"创建表成功");
    }else {
        NSLog(@"创建表失败");
    }
    BOOL result1 = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS gsy_student (gsy_id integer PRIMARY KEY AUTOINCREMENT, gsy_name text NOT NULL, gsy_age integer NOT NULL, gsy_sex text NOT NULL);"];
    if (result1) {
        NSLog(@"创建表成功");
    }else {
        NSLog(@"创建表失败");
    }
}

// 插入数据
-(void)insertData {
    NSString *name = [NSString stringWithFormat:@"Gsony%@",@(markStudent)];
    int age = markStudent;
    NSString *sex = @"男";
    markStudent ++;
    
    // 1.executeUpdate:不确定的参数用？来占位（后面参数必须是oc对象，；代表语句结束）
    BOOL result = [db executeUpdate:@"INSERT INTO t_student (name, age, sex) VALUES (?,?,?)",name,@(age),sex];
    
    // 2.executeUpdateWithForamat：不确定的参数用%@，%d等来占位 （参数为原始数据类型，执行语句不区分大小写）
    // BOOL result = [_db executeUpdateWithFormat:@"insert into t_student (name,age, sex) values (%@,%i,%@)",name,age,sex];
    
    // 3.参数是数组的使用方式
    // BOOL result = [_db executeUpdate:@"INSERT INTO t_student(name,age,sex) VALUES (?,?,?);" withArgumentsInArray:@[name,@(age),sex]];
    
    if (result) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }
    
    // gsy_student 表
    BOOL result1 = [db executeUpdate:@"INSERT INTO gsy_student (gsy_name, gsy_age, gsy_sex) VALUES (?,?,?)",name,@(age),sex];
    
    if (result1) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }

}

// 删除数据
-(void)deleteData {
    int idNum = 11;
    BOOL result = [db executeUpdate:@"delete from t_student where id = ?",@(idNum)];
//    BOOL result = [db executeUpdateWithFormat:@"delete from gsy_student where gsy_name = %@",@"Gsony4"];
    
    if (result) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
}

// 修改数据
-(void)changeData {
    NSString *newName = @"GGGGsony=";
    NSString *oldName = @"Gsony4";
    BOOL result = [db executeUpdate:@"update t_student set name = ? where name = ?",newName,oldName];
    if (result) {
        NSLog(@"修改成功");
    } else {
        NSLog(@"修改失败");
    }
    
}

// 查询数据
-(void)printData {
    FMResultSet *resultSet = [db executeQuery:@"select * from t_student"];
    
    // 遍历结果集合
    while ([resultSet next]) {
        int idNum = [resultSet intForColumn:@"id"];
        NSString *name = [resultSet objectForColumn:@"name"];
        int age = [resultSet intForColumn:@"age"];
        NSString *sex = [resultSet objectForColumn:@"sex"];
        
        NSLog(@"学号：%@ 姓名：%@ 年龄：%@ 性别：%@",@(idNum),name,@(age),sex);
    }
}
@end
